﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using AD.Model;

namespace AD.DAL
{
    public class NoticeDAL : DALBase
    {
        public static int Insert(NoticeMod hepler)
        {
            try
            {
                string sql = "insert into tab_notice_info(Title,Url,Content) values('{0}','{1}','{2}')";
                sql = string.Format(sql, hepler.Title, hepler.Url, hepler.Content);
                return ExecuteNonQuery(sql);
            }
            catch
            {
            }
            return 0;
        }
        public static int DeleteById(int id) {
            try
            {
                string sql = "delete tab_notice_info where AutoID="+id;
                return ExecuteNonQuery(sql);
            }
            catch
            {
            }
            return 0;
        }
        public static int Update(NoticeMod hepler)
        {
            try
            {
                string sql = "update tab_notice_info set Title='{0}',Url='{1}',Content='{2}' where AutoID={3}";
                sql = string.Format(sql, hepler.Title, hepler.Url, hepler.Content, hepler.AutoID);
                return ExecuteNonQuery(sql);
            }
            catch
            {
            }
            return 0;
        }
        public static IList<NoticeMod> QueryByPage(string filter, int pageSize, int index, out int MaxSize)
        {
            MaxSize = 0;
            try
            {
                MaxSize = Count(filter);
                string sql = "select top {0} * from tab_notice_info where (AutoID NOT IN (select top {1} AutoID from tab_notice_info order by AutoID desc)) order by AutoID desc";
                sql = string.Format(sql, pageSize, index * pageSize);
                if (!string.IsNullOrEmpty(filter))
                {
                    sql = "select top {0} * from tab_notice_info where {2} and (AutoID NOT IN (select top {1} AutoID from tab_notice_info where {2} order by AutoID desc)) order by AutoID desc";
                    sql = string.Format(sql, pageSize, index * pageSize, filter);
                }
                DataTable dt = ExecuteDataTable(sql);
                if (dt == null || dt.Rows.Count <= 0) return null;
                IList<NoticeMod> accountFlows = new List<NoticeMod>();
                foreach (DataRow row in dt.Rows)
                {
                    NoticeMod user = SetModel(row);
                    accountFlows.Add(user);
                }
                return accountFlows;
            }
            catch { return null; }
        }
        public static int Count(string filter)
        {
            return QueryFilterToTable(filter).Rows.Count;
        }

        public static NoticeMod QueryFrist(string filter)
        {
            IList<NoticeMod> tmp = QueryFilterToList(filter);
            if (tmp != null) return tmp[0];
            return null;
        }
        public static NoticeMod QueryById(int id)
        {
            return QueryById(id + "");
        }
        public static NoticeMod QueryById(string id)
        {
            IList<NoticeMod> tmp = QueryFilterToList(" AutoID=" + id);
            if (tmp != null) return tmp[0];
            return null;
        }
        public static List<NoticeMod> QueryFilterToList(string filter)
        {
            try
            {
                DataTable dt = QueryFilterToTable(filter);
                if (dt == null || dt.Rows.Count <= 0) return null;
                List<NoticeMod> users = new List<NoticeMod>();
                foreach (DataRow row in dt.Rows)
                {
                    NoticeMod user = SetModel(row);
                    users.Add(user);
                }
                return users;
            }
            catch
            {
            }
            return null;
        }
      

        public static DataTable QueryFilterToTable(string filter)
        {
            try
            {
                string sql = "Select * from tab_notice_info ";
                if (!string.IsNullOrEmpty(filter))
                    sql += " where " + filter;
                //sql  += " order by AutoID desc";
                return ExecuteDataTable(sql);
            }
            catch
            {
            }
            return null;
        }

        #region
        static NoticeMod SetModel(DataRow row)
        {
            NoticeMod user = new NoticeMod();
            user.AutoID = int.Parse(row["AutoID"].ToString());

            if (row["Title"] != DBNull.Value)
                user.Title = row["Title"].ToString();

            if (row["Content"] != DBNull.Value)
                user.Content = row["Content"].ToString();

            if (row["Url"] != DBNull.Value)
                user.Url = row["Url"].ToString();
            return user;
        }

        #endregion
    }
}